/*
 * Copyright (c) 2009, 2010, 2011, 2012, 2013, B3log Team
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.b3log.solo.repository.impl;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.b3log.latke.Keys;
import org.b3log.latke.repository.*;
import org.b3log.latke.repository.jdbc.util.Connections;
import org.b3log.latke.util.CollectionUtils;
import org.b3log.solo.model.Article;
import org.b3log.solo.repository.ArticleRepository;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;


/**
 * Article repository.
 *
 * @author <a href="mailto:DL88250@gmail.com">Liang Ding</a>
 * @version 1.0.3.10, Jan 31, 2013
 * @since 0.3.1
 */
public final class ArticleRepositoryImpl extends AbstractRepository implements ArticleRepository {

    /**
     * Logger.
     */
    private static final Logger LOGGER = Logger.getLogger(ArticleRepositoryImpl.class.getName());

    /**
     * Singleton.
     */
    private static final ArticleRepositoryImpl SINGLETON = new ArticleRepositoryImpl(Article.ARTICLE);

    /**
     * Random range.
     */
    private static final double RANDOM_RANGE = 0.1D;

    @Override
    public JSONObject getByAuthorEmail(final String authorEmail, final int currentPageNum, final int pageSize)
        throws RepositoryException {
        final Query query = new Query().setFilter(CompositeFilterOperator.and(new PropertyFilter(Article.ARTICLE_AUTHOR_EMAIL, FilterOperator.EQUAL, authorEmail), new PropertyFilter(Article.ARTICLE_IS_PUBLISHED, FilterOperator.EQUAL, true))).addSort(Article.ARTICLE_UPDATE_DATE, SortDirection.DESCENDING).addSort(Article.ARTICLE_PUT_TOP, SortDirection.DESCENDING).setCurrentPageNum(currentPageNum).setPageSize(pageSize).setPageCount(
            1);

        return get(query);
    }

    @Override
    public JSONObject getByPermalink(final String permalink) throws RepositoryException {
        final Query query = new Query().setFilter(new PropertyFilter(Article.ARTICLE_PERMALINK, FilterOperator.EQUAL, permalink)).setPageCount(
            1);

        final JSONObject result = get(query);
        final JSONArray array = result.optJSONArray(Keys.RESULTS);

        if (0 == array.length()) {
            return null;
        }

        return array.optJSONObject(0);
    }

    @Override
    public List<JSONObject> getRecentArticles(final int fetchSize) throws RepositoryException {
        final Query query = new Query();

        query.setFilter(new PropertyFilter(Article.ARTICLE_IS_PUBLISHED, FilterOperator.EQUAL, true));
        query.addSort(Article.ARTICLE_UPDATE_DATE, SortDirection.DESCENDING);
        query.setCurrentPageNum(1);
        query.setPageSize(fetchSize);
        query.setPageCount(1);

        final JSONObject result = get(query);
        final JSONArray array = result.optJSONArray(Keys.RESULTS);

        return CollectionUtils.jsonArrayToList(array);
    }

    @Override
    public List<JSONObject> getMostCommentArticles(final int num) throws RepositoryException {
        final Query query = new Query().addSort(Article.ARTICLE_COMMENT_COUNT, SortDirection.DESCENDING).addSort(Article.ARTICLE_UPDATE_DATE, SortDirection.DESCENDING).setFilter(new PropertyFilter(Article.ARTICLE_IS_PUBLISHED, FilterOperator.EQUAL, true)).setCurrentPageNum(1).setPageSize(num).setPageCount(
            1);

        final JSONObject result = get(query);
        final JSONArray array = result.optJSONArray(Keys.RESULTS);

        return CollectionUtils.jsonArrayToList(array);
    }

    @Override
    public List<JSONObject> getMostViewCountArticles(final int num) throws RepositoryException {
        final Query query = new Query();

        query.addSort(Article.ARTICLE_VIEW_COUNT, SortDirection.DESCENDING).addSort(Article.ARTICLE_UPDATE_DATE, SortDirection.DESCENDING);
        query.setFilter(new PropertyFilter(Article.ARTICLE_IS_PUBLISHED, FilterOperator.EQUAL, true));
        query.setCurrentPageNum(1);
        query.setPageSize(num);
        query.setPageCount(1);

        final JSONObject result = get(query);
        final JSONArray array = result.optJSONArray(Keys.RESULTS);

        return CollectionUtils.jsonArrayToList(array);
    }

    @Override
    public JSONObject getPreviousArticle(final String articleId) throws RepositoryException {
        final JSONObject currentArticle = get(articleId);
        final Date currentArticleCreateDate = (Date) currentArticle.opt(Article.ARTICLE_CREATE_DATE);

        final Query query = new Query().setFilter(CompositeFilterOperator.and(new PropertyFilter(Article.ARTICLE_CREATE_DATE, FilterOperator.LESS_THAN, currentArticleCreateDate), new PropertyFilter(Article.ARTICLE_IS_PUBLISHED, FilterOperator.EQUAL, true))).addSort(Article.ARTICLE_CREATE_DATE, SortDirection.DESCENDING).setCurrentPageNum(1).setPageSize(1).setPageCount(1).addProjection(Article.ARTICLE_TITLE, String.class).addProjection(
            Article.ARTICLE_PERMALINK, String.class);

        final JSONObject result = get(query);
        final JSONArray array = result.optJSONArray(Keys.RESULTS);

        if (1 != array.length()) {
            return null;
        }

        final JSONObject ret = new JSONObject();
        final JSONObject article = array.optJSONObject(0);

        try {
            ret.put(Article.ARTICLE_TITLE, article.getString(Article.ARTICLE_TITLE));
            ret.put(Article.ARTICLE_PERMALINK, article.getString(Article.ARTICLE_PERMALINK));
        } catch (final JSONException e) {
            throw new RepositoryException(e);
        }

        return ret;
    }

    @Override
    public JSONObject getNextArticle(final String articleId) throws RepositoryException {
        final JSONObject currentArticle = get(articleId);
        final Date currentArticleCreateDate = (Date) currentArticle.opt(Article.ARTICLE_CREATE_DATE);

        final Query query = new Query().setFilter(CompositeFilterOperator.and(new PropertyFilter(Article.ARTICLE_CREATE_DATE, FilterOperator.GREATER_THAN, currentArticleCreateDate), new PropertyFilter(Article.ARTICLE_IS_PUBLISHED, FilterOperator.EQUAL, true))).addSort(Article.ARTICLE_CREATE_DATE, SortDirection.ASCENDING).setCurrentPageNum(1).setPageSize(1).setPageCount(1).addProjection(Article.ARTICLE_TITLE, String.class).addProjection(
            Article.ARTICLE_PERMALINK, String.class);

        final JSONObject result = get(query);
        final JSONArray array = result.optJSONArray(Keys.RESULTS);

        if (1 != array.length()) {
            return null;
        }

        final JSONObject ret = new JSONObject();
        final JSONObject article = array.optJSONObject(0);

        try {
            ret.put(Article.ARTICLE_TITLE, article.getString(Article.ARTICLE_TITLE));
            ret.put(Article.ARTICLE_PERMALINK, article.getString(Article.ARTICLE_PERMALINK));
        } catch (final JSONException e) {
            throw new RepositoryException(e);
        }

        return ret;
    }

    @Override
    public boolean isPublished(final String articleId) throws RepositoryException {
        final JSONObject article = get(articleId);

        if (null == article) {
            return false;
        }

        return article.optBoolean(Article.ARTICLE_IS_PUBLISHED);
    }

    @Override
    public List<JSONObject> getRandomly(final int fetchSize) throws RepositoryException {
        final List<JSONObject> ret = new ArrayList<JSONObject>();

        if (0 == count()) {
            return ret;
        }

        final double mid = Math.random() + RANDOM_RANGE;

        LOGGER.log(Level.FINEST, "Random mid[{0}]", mid);

        Query query = new Query();

        query.setFilter(
            CompositeFilterOperator.and(new PropertyFilter(Article.ARTICLE_RANDOM_DOUBLE, FilterOperator.GREATER_THAN_OR_EQUAL, mid),
            new PropertyFilter(Article.ARTICLE_RANDOM_DOUBLE, FilterOperator.LESS_THAN_OR_EQUAL, mid),
            new PropertyFilter(Article.ARTICLE_IS_PUBLISHED, FilterOperator.EQUAL, true)));
        query.setCurrentPageNum(1);
        query.setPageSize(fetchSize);
        query.setPageCount(1);

        final JSONObject result1 = get(query);
        final JSONArray array1 = result1.optJSONArray(Keys.RESULTS);

        final List<JSONObject> list1 = CollectionUtils.<JSONObject>jsonArrayToList(array1);

        ret.addAll(list1);

        final int reminingSize = fetchSize - array1.length();

        if (0 != reminingSize) { // Query for remains
            query = new Query();
            query.setFilter(
                CompositeFilterOperator.and(new PropertyFilter(Article.ARTICLE_RANDOM_DOUBLE, FilterOperator.GREATER_THAN_OR_EQUAL, 0D),
                new PropertyFilter(Article.ARTICLE_RANDOM_DOUBLE, FilterOperator.LESS_THAN_OR_EQUAL, mid),
                new PropertyFilter(Article.ARTICLE_IS_PUBLISHED, FilterOperator.EQUAL, true)));
            query.setCurrentPageNum(1);
            query.setPageSize(reminingSize);
            query.setPageCount(1);

            final JSONObject result2 = get(query);
            final JSONArray array2 = result2.optJSONArray(Keys.RESULTS);

            final List<JSONObject> list2 = CollectionUtils.<JSONObject>jsonArrayToList(array2);

            ret.addAll(list2);
        }

        return ret;
    }

    /**
     * Gets the {@link ArticleRepositoryImpl} singleton.
     *
     * @return the singleton
     */
    public static ArticleRepositoryImpl getInstance() {
        return SINGLETON;
    }

    /**
     * Private constructor.
     * 
     * @param name the specified name
     */
    private ArticleRepositoryImpl(final String name) {
        super(name);
    }
	
	/**
     * 搜索文章内容
     * @param queryParam
     * @param currentPageNum
     * @param pageSize
     * @return
     * @throws SQLException
     */
	@Override
    public java.util.Map<String,Object> getArticleByQuery(final String queryParam, final int currentPageNum, final int pageSize) throws SQLException{
    	java.util.Map<String,Object> map = new java.util.HashMap<String, Object>();
    	List<JSONObject> list = new ArrayList<JSONObject>();
    	JSONObject jsonObject = null;
    	int count = 0;
    	Connection conn = null;
    	PreparedStatement psmt = null;
    	ResultSet rs = null;
    	try{
    		conn = Connections.getConnection();
        	
        	String sqlCount = "select count(oId) cnt from article where articleTitle like ? or articleContent like ? or articleAbstract like ?";
        	psmt = conn.prepareStatement(sqlCount);
        	String queryParamLikeSql = "%" + queryParam + "%";
        	psmt.setString(1, queryParamLikeSql);
        	psmt.setString(2, queryParamLikeSql);
        	psmt.setString(3, queryParamLikeSql);
        	rs = psmt.executeQuery();
        	
        	if(rs.next()){
        		count = rs.getInt(1);
        	}
        	closeAll(rs, psmt, null);
        	
        	if(count == 0){
        		map.put("data", list);
        		map.put("count", count);
        		return map;
        	}
        	String sql = "select * from article where articleTitle like ? or articleContent like ? or articleAbstract like ? order by articleCreateDate desc limit ?,?";
        	psmt = conn.prepareStatement(sql);
        	
        	psmt.setString(1, queryParamLikeSql);
        	psmt.setString(2, queryParamLikeSql);
        	psmt.setString(3, queryParamLikeSql);
        	psmt.setInt(4, (currentPageNum-1)*pageSize);
        	psmt.setInt(5, pageSize);
        	rs = psmt.executeQuery();
        	ResultSetMetaData rsmd = rs.getMetaData();
        	String[] columnArr = new String[rsmd.getColumnCount()];
        	String[] columnType = new String[rsmd.getColumnCount()];
        	for(int i=0;i<rsmd.getColumnCount();i++){
        		columnArr[i] = rsmd.getColumnName(i+1);
        		columnType[i] = rsmd.getColumnTypeName(i+1);
        	}
        	
        	while (rs.next()) {
        		jsonObject = new JSONObject();
        		for(int i=0;i<columnArr.length;i++){
        			if(columnType[i].equals("VARCHAR")){
        				jsonObject.put(columnArr[i], rs.getString(columnArr[i]));
        			}else if(columnType[i].equals("INT")){
        				jsonObject.put(columnArr[i], rs.getInt(columnArr[i]));
        			}else if(columnType[i].equals("DATETIME")){
        				//java.text.SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
        				//jsonObject.put(columnArr[i], sdf.format(rs.getDate(columnArr[i])));
        				jsonObject.put(columnArr[i], rs.getDate(columnArr[i]));
        			}else if(columnType[i].equals("DOUBLE")){
        				jsonObject.put(columnArr[i], rs.getDouble(columnArr[i]));
        			}else if(columnType[i].equals("CHAR")){
        				jsonObject.put(columnArr[i], rs.getBoolean(columnArr[i]));
        			}
        		}
        		list.add(jsonObject);
    		}
        	
        	map.put("data", list);
        	map.put("count", count);
    	}catch (SQLException e) {
    		throw new SQLException(e);
		}finally {
			closeAll(rs, psmt, conn);
			if(count == 0){
        		map.put("data", list);
        		map.put("count", count);
        		return map;
        	}
		}
		return map;
    }
    
    private void closeAll(ResultSet rs,PreparedStatement psmt,Connection conn) throws SQLException{
		if(rs != null){
    		rs.close();
    		rs = null;
    	}
    	if(psmt != null){
    		psmt.close();
    		psmt = null;
    	}
    	if(conn != null && !conn.isClosed()){
    		conn.close();
    		conn = null;
    	}
    }

}
